{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 准备工作"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [],
   "source": [
    "公众号=\"大象新闻\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [],
   "source": [
    "fn = { \"output\" : { \"公众号_htm_snippets\": \"公众号_htm_snippets_{公众号}.tsv\",\n",
    "                    \"公众号_df\": \"公众号_df_{公众号}.tsv\",\n",
    "                    \"公众号_xlsx\": \"公众号_url_{公众号}.xlsx\" } \\\n",
    "      }\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 采集公众号（requests）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0\n",
      "5\n",
      "10\n",
      "15\n",
      "20\n"
     ]
    }
   ],
   "source": [
    "# 目标url\n",
    "\n",
    "import time\n",
    "import requests\n",
    "import pandas as pd\n",
    "import csv\n",
    "\n",
    "\n",
    "url = \"https://mp.weixin.qq.com/cgi-bin/appmsg\"\n",
    "\n",
    "# 使用Cookie，跳过登陆操作\n",
    "headers = {\n",
    "  \"cookie\": \"pgv_pvid=3519519760; tvfe_boss_uuid=ad30410596b55c1a; pac_uid=0_9091380baf13b; pgv_pvi=7896105984; RK=qZz5Qi2Ml1; ptcz=97278bf2a290b68c669c9a58d859cf6d0fe6bd92105c9e199f30e71f4f8fa80a; ua_id=OwZOs0VPtyeW7ev5AAAAAO1DDneFYoX2Ldzzptel5BQ=; openid2ticket_od-UJwf0QjZJHOh4CAVK4y0siRG0=eCaTYdwc18YVXLz7OWyMdBce5QCI3FwTFeqmTeyfers=; mm_lang=zh_CN; pgv_si=s3124015104; uuid=cc195fd93c5a9ecd01bc2f124e6dbcfe; rand_info=CAESIP/dvKK/uFeeE2tEUmJ1rXCYC6Dpi7UAoSXmey6K8PgA; slave_bizuin=3238651255; data_bizuin=3238651255; bizuin=3238651255; data_ticket=giVuz6aRQyr6ahcqlwlcIEi6P8ru5iBB+2oGaL3YgHwaD4/HXja6+d8qkQCxYjL6; slave_sid=OUFpMnlQTEhOWXBWN1ppOFhsNXB1ekhRVTdaemsxTEljbjY2T01lWWRjbTk0bENhUHpWQzVMTGpyTFczMktwcDV2Z1FRNWt5bU9Mc0xHeEZGdEVRSE42bXRkd0ZkNjJVMjIwY0doMFplbENJcVVoY1ZlcXhJMXZCa2xBNzdEVGl1OGN3VUtlcjlNT2ViSUJT; slave_user=gh_8bdb682553a0; xid=553839af7ba04f3b9e9586f63bf49e4e\",\n",
    "  \"user-agent\": \"Mozilla/5.0 (Linux; Android 6.0; Nexus 5 Build/MRA58N) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/81.0.4044.138 Mobile Safari/537.36\"}\n",
    "\n",
    "data = {\n",
    "    \"token\": \"221388922\",\n",
    "    \"lang\": \"zh_CN\",\n",
    "    \"f\": \"json\",\n",
    "    \"ajax\": \"1\",\n",
    "    \"action\": \"list_ex\",\n",
    "    \"begin\": \"0\",\n",
    "    \"count\": \"5\",\n",
    "    \"query\": \"\",\n",
    "    \"fakeid\": \"MzU1OTYxOTI3NQ==\",\n",
    "    \"type\": \"9\",\n",
    "}\n",
    "\n",
    "\n",
    "\n",
    "content_list=[]\n",
    "\n",
    "for i in range(5):\n",
    "    data[\"begin\"] = i*5\n",
    "    print(data[\"begin\"])\n",
    "    time.sleep(3)\n",
    "    # 使用get方法进行提交\n",
    "    content_json = requests.get(url, headers=headers, params=data).json()\n",
    "#     print(content_json)\n",
    "    # 返回了一个json，里面是每一页的数据\n",
    "    for item in content_json[\"app_msg_list\"]:\n",
    "    # 提取每页文章的标题及对应的url\n",
    "        items = []\n",
    "        items.append(item[\"title\"])\n",
    "        items.append(item[\"link\"])\n",
    "        items.append(item[\"create_time\"])\n",
    "        content_list.append(items)\n",
    "\n",
    "\n",
    "name=['title','link','create_time']\n",
    "test=pd.DataFrame(columns=name,data=content_list)\n",
    "with pd.ExcelWriter(fn[\"output\"][\"公众号_xlsx\"].format(公众号=\"大象公会_requests\")) as writer:\n",
    "    test.to_excel(writer)\n",
    "\n",
    "# test.to_csv(\"../微信公众号爬虫_zhichao/南方周末.csv\",mode='a',encoding='utf-8')\n",
    "# print(\"保存成功\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 采集公众号（selenium）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "from lxml.html import fromstring\n",
    "import time\n",
    "from random import random\n",
    "\n",
    "# when selenium main_content is used\n",
    "# Parses an HTML document from a string constant.  Returns the root nood\n",
    "# root = fromstring(df.loc[1,\"html_snippets\"]) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 使用Selenium\n",
    "* 要更改 opts.binary_location 至自己本地的Chrome浏览器，建议portable\n",
    "* Chrome浏览器 和 chromedriver.exe要同版本号到小数后一位\n",
    "* 要确保可以 开启浏览器机器人\n",
    "* 要确保浏览器机器人 可以打开网页 driver.get(\"https://mp.weixin.qq.com\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "D:\\A\\lib\\site-packages\\ipykernel_launcher.py:18: DeprecationWarning: use options instead of chrome_options\n"
     ]
    }
   ],
   "source": [
    "from selenium import webdriver\n",
    "from selenium.webdriver.common.desired_capabilities import DesiredCapabilities\n",
    "\n",
    "#caps=dict()\n",
    "#caps[\"pageLoadStrategy\"] = \"none\"   # Do not wait for full page load\n",
    "\n",
    "opts = webdriver.ChromeOptions()\n",
    "opts.add_argument('--no-sandbox')#解决DevToolsActivePort文件不存在的报错\n",
    "opts.add_argument('window-size=1920x3000') #指定浏览器分辨率\n",
    "opts.add_argument('--disable-gpu') #谷歌文档提到需要加上一这个属性来规避bug\n",
    "opts.add_argument('--hide-scrollbars') #隐藏滚动条, 应对些特殊页面\n",
    "#opts.add_argument('blink-settings=imagesEnabled=false') #不加载图片, 提升速度\n",
    "#opts.add_argument('--headless') #浏览器不提供可视化页面. linux下如果系统不支持可视化不加这条会启动失败\n",
    "\n",
    "opts.binary_location = r\"C:\\Users\\dell\\AppData\\Local\\Google\\Chrome\\Application\\chrome.exe\" #\"H:\\_coding_\\Gitee\\InternetNewMedia\\CapstonePrj2016\\chromedriver.exe\"  \n",
    "\n",
    "# \"H:\\_coding_\\Gitee\\InternetNewMedia\\CapstonePrj2016\\chromedriver.exe\"  \n",
    "driver = webdriver.Chrome( chrome_options = opts) #desired_capabilities=caps,"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [],
   "source": [
    "driver.get(\"https://mp.weixin.qq.com\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 填表登入"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "selenium 的定位方法\n",
    "* find_element_by_id &ensp;&ensp;&ensp;  根据标签id定位\n",
    "* find_element_by_name   &ensp;&ensp;&ensp; 根据标签的name定位\n",
    "* find_element_by_xpath  &ensp;&ensp;&ensp; 根据xpath定位\n",
    "* find_element_by_link_text  &ensp;&ensp;&ensp; 通过文字链接来定位元素\n",
    "* find_element_by_partial_link_text  &ensp;&ensp;&ensp;  通过文字链接来定位元素\n",
    "* find_element_by_tag_name  &ensp;&ensp;&ensp;  根据标签的名字定位\n",
    "* find_element_by_class_name  &ensp;&ensp;&ensp; 通过class name 定位\n",
    "* find_element_by_css_selector  &ensp;&ensp;&ensp;  根据元素属性来定位"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<selenium.webdriver.remote.webelement.WebElement (session=\"5bff321a6cdb61604ade83941a4a0db6\", element=\"b8dc7ef7-9ddb-4966-862c-e7a967e8fe10\")>"
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "payload =  {\"account\": \"2262955615@qq.com\", \"password\": \"922887yux+-*%\"}\n",
    "# payload =  {\"account\": \"NFUHacks@163.com\", \"password\": \"NFU706947580\"}\n",
    "driver.find_element_by_xpath('//div[@class=\"login__type__container login__type__container__scan\"]/a')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "WebDriver 常用方法：\n",
    "* clear()清楚文本\n",
    "* send_keys(values)模拟按键输入\n",
    "* click()模拟点击\n",
    "* submit模拟提交"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [],
   "source": [
    "driver.find_element_by_xpath('//form[@class=\"login_form\"]//input[@name=\"account\"]').clear()\n",
    "driver.find_element_by_xpath('//form[@class=\"login_form\"]//input[@name=\"account\"]').send_keys(payload['account'])\n",
    "driver.find_element_by_xpath('//form[@class=\"login_form\"]//input[@name=\"password\"]').clear()\n",
    "driver.find_element_by_xpath('//form[@class=\"login_form\"]//input[@name=\"password\"]').send_keys(payload['password'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [],
   "source": [
    "driver.find_element_by_xpath('//div[@class=\"login_btn_panel\"]/a').click()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 点选单"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    " 其他常用方法\n",
    "* size：返回元素的尺寸\n",
    "* text：获取元素的文本\n",
    "* get_attribute：获取属性值  &ensp;&ensp;&ensp; get_attribute('innerHTML')获取元素内的全部HTML\n",
    "* is_displayed()：设置该元素用户是否可见"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'展开'"
      ]
     },
     "execution_count": 93,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "element = driver.find_element_by_xpath('//a[@id=\"m_open\"]')\n",
    "element.click()\n",
    "main_content = element.get_attribute('innerHTML')\n",
    "main_content"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [],
   "source": [
    "driver.execute_script(\"window.scrollTo(0,document.body.scrollHeight)\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'https://mp.weixin.qq.com/cgi-bin/appmsg?begin=0&count=10&t=media/appmsg_list&type=10&action=list&token=39505017&lang=zh_CN'"
      ]
     },
     "execution_count": 95,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "element = driver.find_element_by_xpath('//li[@title[contains(.,\"素材管理\")]]/a') \n",
    "# main_content = element.get_attribute('innerHTML')\n",
    "# main_content\n",
    "url2= element.get_attribute(\"href\")\n",
    "url2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [],
   "source": [
    "driver.get(url2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 新建图文消息"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [],
   "source": [
    "element = driver.find_element_by_xpath('//*[text()[contains(.,\"新建图文消息\")]]') \n",
    "main_content = element.get_attribute('innerHTML')\n",
    "main_content\n",
    "element.click()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['CDwindow-6E037DF482618FFCB35724FCB0B52C01', 'CDwindow-4ED9FA1EAA3B240999BCC2A2523ABE9A']\n"
     ]
    }
   ],
   "source": [
    "print (driver.window_handles)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 新建图文消息开了另一分视窗，所以要切换 switch_to \n",
    "driver.switch_to.window(driver.window_handles[-1])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 超链接"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                超链接              \n"
     ]
    }
   ],
   "source": [
    "element = driver.find_element_by_xpath('//*[text()[contains(.,\"超链接\")]]') \n",
    "main_content = element.get_attribute('innerHTML')\n",
    "print(main_content)\n",
    "element.click()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "选择其他公众号\n"
     ]
    }
   ],
   "source": [
    "# 点 选择其他公众号\n",
    "element = driver.find_element_by_xpath('//*[text()[contains(.,\"选择其他公众号\")]]') \n",
    "main_content = element.get_attribute('innerHTML')\n",
    "print(main_content)\n",
    "element.click()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {},
   "outputs": [],
   "source": [
    "driver.find_element_by_xpath('//form//div[@class=\"inner_link_account_area\"]//input[@class=\"weui-desktop-form__input\"]').clear()\n",
    "driver.find_element_by_xpath('//form//div[@class=\"inner_link_account_area\"]//input[@class=\"weui-desktop-form__input\"]').send_keys(公众号)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<div class=\"weui-desktop-icon weui-desktop-icon__inputSearch weui-desktop-icon__small\"><!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <svg width=\"16\" height=\"16\" viewBox=\"0 0 16 16\" xmlns=\"http://www.w3.org/2000/svg\"><path d=\"M11.33 10.007l4.273 4.273a.502.502 0 0 1 .005.709l-.585.584a.499.499 0 0 1-.709-.004L10.046 11.3a6.278 6.278 0 1 1 1.284-1.294zm.012-3.729a5.063 5.063 0 1 0-10.127 0 5.063 5.063 0 0 0 10.127 0z\"></path></svg> <!----> <!----> <!----> <!----></div>\n"
     ]
    }
   ],
   "source": [
    "# 点放大镜搜\n",
    "element = driver.find_element_by_xpath('//button[@class=\"weui-desktop-icon-btn weui-desktop-search__btn\"]')\n",
    "main_content = element.get_attribute('innerHTML')\n",
    "print(main_content)\n",
    "element.click()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<li class=\"inner_link_account_item\"><div class=\"weui-desktop-vm_primary\"><img src=\"http://mmbiz.qpic.cn/mmbiz_png/WAuTS5GGG2QQTXQQU6oYE1v4BIv0RSqS7xM5KROcZfLcibdBrMEw1qdUtwwictFAyjNqVQKCkVFtYz8mpL53hUug/0?wx_fmt=png\" class=\"inner_link_account_avatar\"> <strong class=\"inner_link_account_nickname\">大象新闻</strong> <i class=\"inner_link_account_wechat\">微信号：dxxinwen</i></div> <div class=\"weui-desktop-vm_default inner_link_account_type\">订阅号</div></li><li class=\"inner_link_account_item\"><div class=\"weui-desktop-vm_primary\"><img src=\"http://mmbiz.qpic.cn/mmbiz_png/xlz6W7DvhlWj9hrSRCVSltdokA5ibDiarln9YrVZpXuIswNI8yhK8DiaBSj6zYfasibibyRfbkToluoG8L0H2pJ1Y2A/0?wx_fmt=png\" class=\"inner_link_account_avatar\"> <strong class=\"inner_link_account_nickname\">河南卫视</strong> <i class=\"inner_link_account_wechat\">微信号：hntv-ws</i></div> <div class=\"weui-desktop-vm_default inner_link_account_type\">订阅号</div></li><li class=\"inner_link_account_item\"><div class=\"weui-desktop-vm_primary\"><img src=\"http://mmbiz.qpic.cn/mmbiz_png/6SicykIlchXkLsQgy74b6clgmlGxCzmxzvyM0H15JVWDG3P1uNfndP4al3ZCM50NqefFewytAfLHGbPAusHMJSw/0?wx_fmt=png\" class=\"inner_link_account_avatar\"> <strong class=\"inner_link_account_nickname\">大象融媒集团</strong> <i class=\"inner_link_account_wechat\">微信号：DaxiangMergedMedia</i></div> <div class=\"weui-desktop-vm_default inner_link_account_type\">订阅号</div></li><li class=\"inner_link_account_item\"><div class=\"weui-desktop-vm_primary\"><img src=\"http://mmbiz.qpic.cn/mmbiz_png/zvOtibYZ51RIuHIoBH9B7kyQeqRGy1CLRIOeeS138e6hJK9KQruhGchENMeylpo5oibjYZsf4coSTCibiatLr3seTg/0?wx_fmt=png\" class=\"inner_link_account_avatar\"> <strong class=\"inner_link_account_nickname\">大象快车</strong> <i class=\"inner_link_account_wechat\">微信号：svipbus</i></div> <div class=\"weui-desktop-vm_default inner_link_account_type\">服务号</div></li><li class=\"inner_link_account_item\"><div class=\"weui-desktop-vm_primary\"><img src=\"http://mmbiz.qpic.cn/mmbiz_png/EPxXqgkqjnZo9OV11sHC4tZoltyHHSibPC9m1TKawpKxm234s82PZ7hpicdfbx7TJBo6klZQ2nL9QKn5z70M7uibQ/0?wx_fmt=png\" class=\"inner_link_account_avatar\"> <strong class=\"inner_link_account_nickname\">大象慢生活</strong> <i class=\"inner_link_account_wechat\">微信号：ycenclub</i></div> <div class=\"weui-desktop-vm_default inner_link_account_type\">订阅号</div></li>\n"
     ]
    }
   ],
   "source": [
    "element = driver.find_element_by_xpath('//ul[@class=\"inner_link_account_list\"]')\n",
    "main_content = element.get_attribute('innerHTML')\n",
    "print(main_content)\n",
    "公众号SERP = main_content"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 解析\n",
    "root = fromstring(公众号SERP) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {},
   "outputs": [],
   "source": [
    "主 = root.xpath('//li[@class=\"inner_link_account_item\"]')\n",
    "\n",
    "account_list = []\n",
    "for e in 主:\n",
    "    account_nickname = e.xpath('./div/strong[@class=\"inner_link_account_nickname\"]')[0].text\n",
    "    account_wechat = e.xpath('./div/i[@class=\"inner_link_account_wechat\"]')[0].text\n",
    "    account_img = e.xpath('./div/img/@src')[0]\n",
    "    account = {\"nickname\": account_nickname, \"wechat\": account_wechat, \"img\": account_img,}\n",
    "    account_list.append(account)\n",
    "\n",
    "df_account = pd.DataFrame(account_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>nickname</th>\n",
       "      <th>wechat</th>\n",
       "      <th>img</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>大象新闻</td>\n",
       "      <td>微信号：dxxinwen</td>\n",
       "      <td>http://mmbiz.qpic.cn/mmbiz_png/WAuTS5GGG2QQTXQ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>河南卫视</td>\n",
       "      <td>微信号：hntv-ws</td>\n",
       "      <td>http://mmbiz.qpic.cn/mmbiz_png/xlz6W7DvhlWj9hr...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>大象融媒集团</td>\n",
       "      <td>微信号：DaxiangMergedMedia</td>\n",
       "      <td>http://mmbiz.qpic.cn/mmbiz_png/6SicykIlchXkLsQ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>大象快车</td>\n",
       "      <td>微信号：svipbus</td>\n",
       "      <td>http://mmbiz.qpic.cn/mmbiz_png/zvOtibYZ51RIuHI...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>大象慢生活</td>\n",
       "      <td>微信号：ycenclub</td>\n",
       "      <td>http://mmbiz.qpic.cn/mmbiz_png/EPxXqgkqjnZo9OV...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  nickname                  wechat  \\\n",
       "0     大象新闻            微信号：dxxinwen   \n",
       "1     河南卫视             微信号：hntv-ws   \n",
       "2   大象融媒集团  微信号：DaxiangMergedMedia   \n",
       "3     大象快车             微信号：svipbus   \n",
       "4    大象慢生活            微信号：ycenclub   \n",
       "\n",
       "                                                 img  \n",
       "0  http://mmbiz.qpic.cn/mmbiz_png/WAuTS5GGG2QQTXQ...  \n",
       "1  http://mmbiz.qpic.cn/mmbiz_png/xlz6W7DvhlWj9hr...  \n",
       "2  http://mmbiz.qpic.cn/mmbiz_png/6SicykIlchXkLsQ...  \n",
       "3  http://mmbiz.qpic.cn/mmbiz_png/zvOtibYZ51RIuHI...  \n",
       "4  http://mmbiz.qpic.cn/mmbiz_png/EPxXqgkqjnZo9OV...  "
      ]
     },
     "execution_count": 107,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_account"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<div class=\"weui-desktop-vm_primary\"><img src=\"http://mmbiz.qpic.cn/mmbiz_png/WAuTS5GGG2QQTXQQU6oYE1v4BIv0RSqS7xM5KROcZfLcibdBrMEw1qdUtwwictFAyjNqVQKCkVFtYz8mpL53hUug/0?wx_fmt=png\" class=\"inner_link_account_avatar\"> <strong class=\"inner_link_account_nickname\">大象新闻</strong> <i class=\"inner_link_account_wechat\">微信号：dxxinwen</i></div> <div class=\"weui-desktop-vm_default inner_link_account_type\">订阅号</div>\n"
     ]
    }
   ],
   "source": [
    "element = driver.find_element_by_xpath('//ul[@class=\"inner_link_account_list\"]/li')\n",
    "main_content = element.get_attribute('innerHTML')\n",
    "print(main_content)\n",
    "element.click()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'\\n跳转_input = driver.find_element_by_xpath(\\'//span[@class=\"weui-desktop-pagination__form\"]/input\\')\\n跳转_a = driver.find_element_by_xpath(\\'//span[@class=\"weui-desktop-pagination__form\"]/a\\')\\n跳转_input.clear()\\n跳转_input.send_keys(2)\\n跳转_a.click()\\n'"
      ]
     },
     "execution_count": 109,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 跳转testing\n",
    "'''\n",
    "跳转_input = driver.find_element_by_xpath('//span[@class=\"weui-desktop-pagination__form\"]/input')\n",
    "跳转_a = driver.find_element_by_xpath('//span[@class=\"weui-desktop-pagination__form\"]/a')\n",
    "跳转_input.clear()\n",
    "跳转_input.send_keys(2)\n",
    "跳转_a.click()\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[1, 35]\n",
      "False\n"
     ]
    }
   ],
   "source": [
    "# 跳转上限\n",
    "l_e = driver.find_elements_by_xpath('//label[@class=\"weui-desktop-pagination__num\"]')\n",
    "l_e_int  = [int(x.text) for x in l_e] \n",
    "print (l_e_int)\n",
    "print (l_e_int[0]==l_e_int[-1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35]\n"
     ]
    }
   ],
   "source": [
    "pages = list(range(l_e_int[0],l_e_int[-1]+1 ))\n",
    "#print(pages[0:2])\n",
    "pages = list(range(1,l_e_int[-1]+1 ))\n",
    "print(pages)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 循环/遍历"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {},
   "outputs": [],
   "source": [
    "# global varialbes \n",
    "html_raw = dict()\n",
    "main_content =\"\"\n",
    "element = None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "metadata": {},
   "outputs": [],
   "source": [
    "def process_pages (pages):\n",
    "    for p in pages:\n",
    "        print (p,end='\\t')\n",
    "\n",
    "        跳转_input = driver.find_element_by_xpath('//span[@class=\"weui-desktop-pagination__form\"]/input')\n",
    "        跳转_a = driver.find_element_by_xpath('//span[@class=\"weui-desktop-pagination__form\"]/a')\n",
    "        跳转_input.clear()\n",
    "        跳转_input.send_keys(p)\n",
    "        跳转_a.click()\n",
    "\n",
    "        time.sleep(20+80*random())\n",
    "\n",
    "        element = driver.find_element_by_xpath('//div[@class=\"inner_link_article_list\"]')\n",
    "        main_content = element.get_attribute('innerHTML')\n",
    "        #print(main_content)\n",
    "        html_raw[p] = main_content"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1\t2\t3\t4\t5\t6\t7\t8\t9\t10\t11\t12\t13\t14\t15\t16\t17\t18\t19\t20\t21\t22\t23\t24\t25\t26\t27\t28\t29\t30\t31\t32\t33\t34\t35\t"
     ]
    }
   ],
   "source": [
    "process_pages(pages)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>html_snippets</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>10</td>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>11</td>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>12</td>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>13</td>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>14</td>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>15</td>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>16</td>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>17</td>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>18</td>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>19</td>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>20</td>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>21</td>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>22</td>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>23</td>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>24</td>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>25</td>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>26</td>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>27</td>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>28</td>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>29</td>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>30</td>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>31</td>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>32</td>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>33</td>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>34</td>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>35</td>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                        html_snippets\n",
       "1   <div><label class=\"inner_link_article_item\"><s...\n",
       "2   <div><label class=\"inner_link_article_item\"><s...\n",
       "3   <div><label class=\"inner_link_article_item\"><s...\n",
       "4   <div><label class=\"inner_link_article_item\"><s...\n",
       "5   <div><label class=\"inner_link_article_item\"><s...\n",
       "6   <div><label class=\"inner_link_article_item\"><s...\n",
       "7   <div><label class=\"inner_link_article_item\"><s...\n",
       "8   <div><label class=\"inner_link_article_item\"><s...\n",
       "9   <div><label class=\"inner_link_article_item\"><s...\n",
       "10  <div><label class=\"inner_link_article_item\"><s...\n",
       "11  <div><label class=\"inner_link_article_item\"><s...\n",
       "12  <div><label class=\"inner_link_article_item\"><s...\n",
       "13  <div><label class=\"inner_link_article_item\"><s...\n",
       "14  <div><label class=\"inner_link_article_item\"><s...\n",
       "15  <div><label class=\"inner_link_article_item\"><s...\n",
       "16  <div><label class=\"inner_link_article_item\"><s...\n",
       "17  <div><label class=\"inner_link_article_item\"><s...\n",
       "18  <div><label class=\"inner_link_article_item\"><s...\n",
       "19  <div><label class=\"inner_link_article_item\"><s...\n",
       "20  <div><label class=\"inner_link_article_item\"><s...\n",
       "21  <div><label class=\"inner_link_article_item\"><s...\n",
       "22  <div><label class=\"inner_link_article_item\"><s...\n",
       "23  <div><label class=\"inner_link_article_item\"><s...\n",
       "24  <div><label class=\"inner_link_article_item\"><s...\n",
       "25  <div><label class=\"inner_link_article_item\"><s...\n",
       "26  <div><label class=\"inner_link_article_item\"><s...\n",
       "27  <div><label class=\"inner_link_article_item\"><s...\n",
       "28  <div><label class=\"inner_link_article_item\"><s...\n",
       "29  <div><label class=\"inner_link_article_item\"><s...\n",
       "30  <div><label class=\"inner_link_article_item\"><s...\n",
       "31  <div><label class=\"inner_link_article_item\"><s...\n",
       "32  <div><label class=\"inner_link_article_item\"><s...\n",
       "33  <div><label class=\"inner_link_article_item\"><s...\n",
       "34  <div><label class=\"inner_link_article_item\"><s...\n",
       "35  <div><label class=\"inner_link_article_item\"><s..."
      ]
     },
     "execution_count": 115,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame([html_raw]).T\n",
    "df.columns = [\"html_snippets\"]\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Stored 'html_raw' (dict)\n"
     ]
    }
   ],
   "source": [
    "%store html_raw\n",
    "import pickle \n",
    "filehandler = open(\"html_raw\", 'wb') \n",
    "pickle.dump(html_raw, filehandler)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 136,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "35\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>html_snippets</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>10</td>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>11</td>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>12</td>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>13</td>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>14</td>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>15</td>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>16</td>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>17</td>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>18</td>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>19</td>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>20</td>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>21</td>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>22</td>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>23</td>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>24</td>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>25</td>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>26</td>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>27</td>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>28</td>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>29</td>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>30</td>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>31</td>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>32</td>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>33</td>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>34</td>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>35</td>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                        html_snippets\n",
       "1   <div><label class=\"inner_link_article_item\"><s...\n",
       "2   <div><label class=\"inner_link_article_item\"><s...\n",
       "3   <div><label class=\"inner_link_article_item\"><s...\n",
       "4   <div><label class=\"inner_link_article_item\"><s...\n",
       "5   <div><label class=\"inner_link_article_item\"><s...\n",
       "6   <div><label class=\"inner_link_article_item\"><s...\n",
       "7   <div><label class=\"inner_link_article_item\"><s...\n",
       "8   <div><label class=\"inner_link_article_item\"><s...\n",
       "9   <div><label class=\"inner_link_article_item\"><s...\n",
       "10  <div><label class=\"inner_link_article_item\"><s...\n",
       "11  <div><label class=\"inner_link_article_item\"><s...\n",
       "12  <div><label class=\"inner_link_article_item\"><s...\n",
       "13  <div><label class=\"inner_link_article_item\"><s...\n",
       "14  <div><label class=\"inner_link_article_item\"><s...\n",
       "15  <div><label class=\"inner_link_article_item\"><s...\n",
       "16  <div><label class=\"inner_link_article_item\"><s...\n",
       "17  <div><label class=\"inner_link_article_item\"><s...\n",
       "18  <div><label class=\"inner_link_article_item\"><s...\n",
       "19  <div><label class=\"inner_link_article_item\"><s...\n",
       "20  <div><label class=\"inner_link_article_item\"><s...\n",
       "21  <div><label class=\"inner_link_article_item\"><s...\n",
       "22  <div><label class=\"inner_link_article_item\"><s...\n",
       "23  <div><label class=\"inner_link_article_item\"><s...\n",
       "24  <div><label class=\"inner_link_article_item\"><s...\n",
       "25  <div><label class=\"inner_link_article_item\"><s...\n",
       "26  <div><label class=\"inner_link_article_item\"><s...\n",
       "27  <div><label class=\"inner_link_article_item\"><s...\n",
       "28  <div><label class=\"inner_link_article_item\"><s...\n",
       "29  <div><label class=\"inner_link_article_item\"><s...\n",
       "30  <div><label class=\"inner_link_article_item\"><s...\n",
       "31  <div><label class=\"inner_link_article_item\"><s...\n",
       "32  <div><label class=\"inner_link_article_item\"><s...\n",
       "33  <div><label class=\"inner_link_article_item\"><s...\n",
       "34  <div><label class=\"inner_link_article_item\"><s...\n",
       "35  <div><label class=\"inner_link_article_item\"><s..."
      ]
     },
     "execution_count": 136,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_out = df[~df.duplicated()]\n",
    "print (len(df_out))\n",
    "df[~df.duplicated()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 137,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35]\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[1,\n",
       " 2,\n",
       " 3,\n",
       " 4,\n",
       " 5,\n",
       " 6,\n",
       " 7,\n",
       " 8,\n",
       " 9,\n",
       " 10,\n",
       " 11,\n",
       " 12,\n",
       " 13,\n",
       " 14,\n",
       " 15,\n",
       " 16,\n",
       " 17,\n",
       " 18,\n",
       " 19,\n",
       " 20,\n",
       " 21,\n",
       " 22,\n",
       " 23,\n",
       " 24,\n",
       " 25,\n",
       " 26,\n",
       " 27,\n",
       " 28,\n",
       " 29,\n",
       " 30,\n",
       " 31,\n",
       " 32,\n",
       " 33,\n",
       " 34,\n",
       " 35]"
      ]
     },
     "execution_count": 137,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "try_again = list(df[~df.duplicated()].index)\n",
    "print(try_again)\n",
    "try_again = try_again + list (set(pages).difference(set(df.index.values)))\n",
    "try_again"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 赞存档"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 138,
   "metadata": {},
   "outputs": [],
   "source": [
    "filename = fn [\"output\"] [\"公众号_htm_snippets\"] \n",
    "df_out.to_csv(filename.format(公众号=公众号), sep=\"\\t\", encoding=\"utf8\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 139,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "12,15,11,13,15,12,12,11,10,11,17,14,14,14,15,14,16,17,15,18,12,18,9,10,11,12,13,11,10,9,14,11,13,17,5,"
     ]
    }
   ],
   "source": [
    "def parse_html_snippets(_snippet_):\n",
    "    root = fromstring(_snippet_) \n",
    "    title = [x.text for x in root.xpath('//div[@class=\"inner_link_article_title\"]')]\n",
    "    create_time = [x.text for x in root.xpath('//div[@class=\"inner_link_article_date\"]')]\n",
    "    link = [x for x in root.xpath('//a/@href')]\n",
    "    _df_ = pd.DataFrame({\"title\":title, \"create_time\": create_time, \"link\":link})\n",
    "    return(_df_)\n",
    "    \n",
    "l_df = []\n",
    "for p in pages:\n",
    "    _df_ = parse_html_snippets(df.loc[p,\"html_snippets\"])\n",
    "    print (len(_df_), end=\",\")\n",
    "    l_df.append(_df_)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 142,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "      <th>create_time</th>\n",
       "      <th>link</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>谱写青春战歌 交出合格答卷——六集电视政论片《雄关》反响热烈</td>\n",
       "      <td>2020-05-16</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>@疫情中高风险地区入郑人员，这些规定须知</td>\n",
       "      <td>2020-05-16</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>河南80后会计贪污300万买手办  司法拍卖33万宅男抢破头</td>\n",
       "      <td>2020-05-16</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>河南人的福利！年底前全省县城或将5G全覆盖， 惠及14大类行业</td>\n",
       "      <td>2020-05-15</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>格斗狂人郑州约战一龙却等来警察 一龙回应：自导自演的炒作</td>\n",
       "      <td>2020-05-14</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>继肖战粉丝风波后，河南一幼师让孩子应援王俊凯！先上岗再考证让人欢喜让人忧</td>\n",
       "      <td>2020-05-14</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>“郴州大头娃娃”涉事蛋白粉公司回应称产品合格，家长发声</td>\n",
       "      <td>2020-05-13</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>全球最大货航—卡塔尔航空公司开通郑州至多哈货运航线</td>\n",
       "      <td>2020-05-13</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>又进一步！电影院开门这些喜剧能笑哭！</td>\n",
       "      <td>2020-05-13</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>12年，感谢你从未忘记——汶川地震12周年</td>\n",
       "      <td>2020-05-12</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>10</td>\n",
       "      <td>天使原来长这样</td>\n",
       "      <td>2020-05-12</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>11</td>\n",
       "      <td>解密双槐树遗址丨天上的星星看北斗：谁能住在北斗九星的房子里？</td>\n",
       "      <td>2020-05-12</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>12</td>\n",
       "      <td>懵圈？恐怖？开心……开学第一天，孩子们的“画风”都变了</td>\n",
       "      <td>2020-05-11</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>13</td>\n",
       "      <td>解密双槐树遗址丨出土牙雕蚕：这是中国最早的家蚕</td>\n",
       "      <td>2020-05-11</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>14</td>\n",
       "      <td>2020年河南对口招生方案出炉 80所高校面向全省中职学生招生</td>\n",
       "      <td>2020-05-11</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>15</td>\n",
       "      <td>咱河南的航空公司来啦！中州航空今日正式开航</td>\n",
       "      <td>2020-05-10</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>16</td>\n",
       "      <td>郑州“夜经济”新名片：夜游小镇 感受“百年德化”</td>\n",
       "      <td>2020-05-10</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>17</td>\n",
       "      <td>河南广播电视台关于纪录片《百年巨变》的声明</td>\n",
       "      <td>2020-05-09</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>18</td>\n",
       "      <td>电影院复工进入倒计时 但“很多电影院开业不如关门”</td>\n",
       "      <td>2020-05-09</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>19</td>\n",
       "      <td>河南将扩大全日制研究生招生20%以上</td>\n",
       "      <td>2020-05-09</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>20</td>\n",
       "      <td>河南的高校应届毕业生，这个通知你一定得看！</td>\n",
       "      <td>2020-05-09</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                   title create_time  \\\n",
       "0         谱写青春战歌 交出合格答卷——六集电视政论片《雄关》反响热烈  2020-05-16   \n",
       "1                   @疫情中高风险地区入郑人员，这些规定须知  2020-05-16   \n",
       "2         河南80后会计贪污300万买手办  司法拍卖33万宅男抢破头  2020-05-16   \n",
       "3        河南人的福利！年底前全省县城或将5G全覆盖， 惠及14大类行业  2020-05-15   \n",
       "4           格斗狂人郑州约战一龙却等来警察 一龙回应：自导自演的炒作  2020-05-14   \n",
       "5   继肖战粉丝风波后，河南一幼师让孩子应援王俊凯！先上岗再考证让人欢喜让人忧  2020-05-14   \n",
       "6            “郴州大头娃娃”涉事蛋白粉公司回应称产品合格，家长发声  2020-05-13   \n",
       "7              全球最大货航—卡塔尔航空公司开通郑州至多哈货运航线  2020-05-13   \n",
       "8                     又进一步！电影院开门这些喜剧能笑哭！  2020-05-13   \n",
       "9                  12年，感谢你从未忘记——汶川地震12周年  2020-05-12   \n",
       "10                               天使原来长这样  2020-05-12   \n",
       "11        解密双槐树遗址丨天上的星星看北斗：谁能住在北斗九星的房子里？  2020-05-12   \n",
       "12           懵圈？恐怖？开心……开学第一天，孩子们的“画风”都变了  2020-05-11   \n",
       "13               解密双槐树遗址丨出土牙雕蚕：这是中国最早的家蚕  2020-05-11   \n",
       "14       2020年河南对口招生方案出炉 80所高校面向全省中职学生招生  2020-05-11   \n",
       "15                 咱河南的航空公司来啦！中州航空今日正式开航  2020-05-10   \n",
       "16              郑州“夜经济”新名片：夜游小镇 感受“百年德化”  2020-05-10   \n",
       "17                 河南广播电视台关于纪录片《百年巨变》的声明  2020-05-09   \n",
       "18             电影院复工进入倒计时 但“很多电影院开业不如关门”  2020-05-09   \n",
       "19                    河南将扩大全日制研究生招生20%以上  2020-05-09   \n",
       "20                 河南的高校应届毕业生，这个通知你一定得看！  2020-05-09   \n",
       "\n",
       "                                                 link  \n",
       "0   http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...  \n",
       "1   http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...  \n",
       "2   http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...  \n",
       "3   http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...  \n",
       "4   http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...  \n",
       "5   http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...  \n",
       "6   http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...  \n",
       "7   http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...  \n",
       "8   http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...  \n",
       "9   http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...  \n",
       "10  http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...  \n",
       "11  http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...  \n",
       "12  http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...  \n",
       "13  http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...  \n",
       "14  http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...  \n",
       "15  http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...  \n",
       "16  http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...  \n",
       "17  http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...  \n",
       "18  http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...  \n",
       "19  http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...  \n",
       "20  http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...  "
      ]
     },
     "execution_count": 142,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_url_out = pd.concat(l_df).reset_index(drop=True)\n",
    "df_url_out.loc[0:20]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 143,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "      <th>create_time</th>\n",
       "      <th>link</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>441</td>\n",
       "      <td>免费送票30张 | 《平原上的夏洛克》，你来我就送</td>\n",
       "      <td>2019-11-22</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>442</td>\n",
       "      <td>2020年放假安排来了！</td>\n",
       "      <td>2019-11-21</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>443</td>\n",
       "      <td>《冰雪奇缘2》 Elsa狂换衣，又美又飒，大型种草现场没错了！买它！</td>\n",
       "      <td>2019-11-21</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>444</td>\n",
       "      <td>世界电视日：电视都给你带来过哪些童年阴影</td>\n",
       "      <td>2019-11-21</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>445</td>\n",
       "      <td>打一针管13年！全球首个男性避孕针剂通过试验 网友纷纷@刘阳</td>\n",
       "      <td>2019-11-21</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>446</td>\n",
       "      <td>先分后混！郑州垃圾分类“典范”被曝光后，官方这样回应</td>\n",
       "      <td>2019-11-20</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>447</td>\n",
       "      <td>大象独家 | 一桶垃圾的100个小时</td>\n",
       "      <td>2019-11-19</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>448</td>\n",
       "      <td>智能制造“大比武”！这场国字头大赛为何看好郑州？</td>\n",
       "      <td>2019-11-19</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>449</td>\n",
       "      <td>结婚钻戒属于夫妻共同财产还是个人财产？</td>\n",
       "      <td>2018-07-29</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>450</td>\n",
       "      <td>律师说事：“真假离婚”的法律后果</td>\n",
       "      <td>2018-07-24</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                  title create_time  \\\n",
       "441           免费送票30张 | 《平原上的夏洛克》，你来我就送  2019-11-22   \n",
       "442                        2020年放假安排来了！  2019-11-21   \n",
       "443  《冰雪奇缘2》 Elsa狂换衣，又美又飒，大型种草现场没错了！买它！  2019-11-21   \n",
       "444                世界电视日：电视都给你带来过哪些童年阴影  2019-11-21   \n",
       "445      打一针管13年！全球首个男性避孕针剂通过试验 网友纷纷@刘阳  2019-11-21   \n",
       "446          先分后混！郑州垃圾分类“典范”被曝光后，官方这样回应  2019-11-20   \n",
       "447                  大象独家 | 一桶垃圾的100个小时  2019-11-19   \n",
       "448            智能制造“大比武”！这场国字头大赛为何看好郑州？  2019-11-19   \n",
       "449                 结婚钻戒属于夫妻共同财产还是个人财产？  2018-07-29   \n",
       "450                    律师说事：“真假离婚”的法律后果  2018-07-24   \n",
       "\n",
       "                                                  link  \n",
       "441  http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...  \n",
       "442  http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...  \n",
       "443  http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...  \n",
       "444  http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...  \n",
       "445  http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...  \n",
       "446  http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...  \n",
       "447  http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...  \n",
       "448  http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...  \n",
       "449  http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...  \n",
       "450  http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...  "
      ]
     },
     "execution_count": 143,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_url_out.tail(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 156,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "      <th>create_time</th>\n",
       "      <th>link</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>value</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>河南80后会计贪污300万买手办  司法拍卖33万宅男抢破头</td>\n",
       "      <td>2020-05-16</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>河南人的福利！年底前全省县城或将5G全覆盖， 惠及14大类行业</td>\n",
       "      <td>2020-05-15</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>格斗狂人郑州约战一龙却等来警察 一龙回应：自导自演的炒作</td>\n",
       "      <td>2020-05-14</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>继肖战粉丝风波后，河南一幼师让孩子应援王俊凯！先上岗再考证让人欢喜让人忧</td>\n",
       "      <td>2020-05-14</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>“郴州大头娃娃”涉事蛋白粉公司回应称产品合格，家长发声</td>\n",
       "      <td>2020-05-13</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>441</td>\n",
       "      <td>免费送票30张 | 《平原上的夏洛克》，你来我就送</td>\n",
       "      <td>2019-11-22</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>443</td>\n",
       "      <td>《冰雪奇缘2》 Elsa狂换衣，又美又飒，大型种草现场没错了！买它！</td>\n",
       "      <td>2019-11-21</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>444</td>\n",
       "      <td>世界电视日：电视都给你带来过哪些童年阴影</td>\n",
       "      <td>2019-11-21</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>449</td>\n",
       "      <td>结婚钻戒属于夫妻共同财产还是个人财产？</td>\n",
       "      <td>2018-07-29</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>450</td>\n",
       "      <td>律师说事：“真假离婚”的法律后果</td>\n",
       "      <td>2018-07-24</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>252 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                      title create_time  \\\n",
       "value                                                     \n",
       "2            河南80后会计贪污300万买手办  司法拍卖33万宅男抢破头  2020-05-16   \n",
       "3           河南人的福利！年底前全省县城或将5G全覆盖， 惠及14大类行业  2020-05-15   \n",
       "4              格斗狂人郑州约战一龙却等来警察 一龙回应：自导自演的炒作  2020-05-14   \n",
       "5      继肖战粉丝风波后，河南一幼师让孩子应援王俊凯！先上岗再考证让人欢喜让人忧  2020-05-14   \n",
       "6               “郴州大头娃娃”涉事蛋白粉公司回应称产品合格，家长发声  2020-05-13   \n",
       "...                                     ...         ...   \n",
       "441               免费送票30张 | 《平原上的夏洛克》，你来我就送  2019-11-22   \n",
       "443      《冰雪奇缘2》 Elsa狂换衣，又美又飒，大型种草现场没错了！买它！  2019-11-21   \n",
       "444                    世界电视日：电视都给你带来过哪些童年阴影  2019-11-21   \n",
       "449                     结婚钻戒属于夫妻共同财产还是个人财产？  2018-07-29   \n",
       "450                        律师说事：“真假离婚”的法律后果  2018-07-24   \n",
       "\n",
       "                                                    link  \n",
       "value                                                     \n",
       "2      http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...  \n",
       "3      http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...  \n",
       "4      http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...  \n",
       "5      http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...  \n",
       "6      http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...  \n",
       "...                                                  ...  \n",
       "441    http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...  \n",
       "443    http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...  \n",
       "444    http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...  \n",
       "449    http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...  \n",
       "450    http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...  \n",
       "\n",
       "[252 rows x 3 columns]"
      ]
     },
     "execution_count": 156,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# tagging 标记\n",
    "tagging_list = [\"\", \"2020年\", \"大象独家\",\"官方\",\"电影\",\"焦点\",\\\n",
    "                \"青春\",\"全球\",\"最前线\",\"进步\",\"解密\",\"vlog\",\"短视频\",\"创作\"\\\n",
    "                \"腾讯财付通\",\"鹅厂\",\"QQ钱包\",\"QQ红包\",\"QQ\",\\\n",
    "                \"只有一\",\"大咖\",\"听说\",\"图片\",\"照片\",\"小编\",\\\n",
    "                \"腾讯\",\"开学\",\"直播\",\"儿童\",\"粤港澳大湾区\",\"湖北\",\"武汉\",\\\n",
    "                \"入境\", \"隔离\",\"被骗\",\"开学\",\"诈骗\", \"炒股\",\"五一\",\"口罩\",\\\n",
    "                \"银行卡\",\"理财\",\"选股\",\"发票\",\"基金\",\\\n",
    "                \"高校\",\"小学\",\"中学\",\"创业\",\"B站\", \"抖音\",\"小红书\"\\\n",
    "                \"数据\", \"数据赋能\", \"智能\", \"数字孪生\", \"智慧大脑\",\\\n",
    "                \"出行\",\"乘车\",\"公交\",\"乘车码\",\"健康码\", \"专家\",\\\n",
    "                \"央行\",\"新规\", \\\n",
    "                \"微信\", \"微信支付\", \"医院\", \"移动支付\",\"非银行支付\",\"电子支付\",\\\n",
    "                \"互联网金融\", \"金融科技\",\"互联网＋\",\"互联网+金融\",\"普惠金融\",\"虚拟银行\",\\\n",
    "                \"开放\",\"生态\",\"健康码\",\"互联网思维\",\"全球合作伙伴\",\\\n",
    "                \"联合国\", \"城市\", \"粤港澳大湾区\", \"平台\", \"可持续发展\", \"未来\", \"绿色\",\\\n",
    "                \"金融\",\"O2O\",\"农产品\",\"AI\",\"人工智能\"\\\n",
    "                ,\"新冠肺炎\", \"疫\", \"疫情\", \"复工\",\"停课\",\"复课\",\"医患\",\"肺炎\",\"隔离\",\"武汉\",\"危机\",\n",
    "                ] #overwritable\n",
    "v_v_list = []\n",
    "\n",
    "for tag in tagging_list:\n",
    "    index_list = df_url_out [ df_url_out.title.str.contains(tag) ].index.tolist()\n",
    "    v_v_pairs = pd.DataFrame({tag:index_list}).melt().set_index(\"value\")\n",
    "    v_v_list.append(v_v_pairs)\n",
    "\n",
    "df_cat = v_v_list[0]\n",
    "for d in v_v_list:\n",
    "    df_cat.update(d)\n",
    "    \n",
    "# 尚未标记内容\n",
    "df_url_out.loc [ df_cat.query('variable==\"\"').index ]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 157,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "      <th>create_time</th>\n",
       "      <th>link</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>谱写青春战歌 交出合格答卷——六集电视政论片《雄关》反响热烈</td>\n",
       "      <td>2020-05-16</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>@疫情中高风险地区入郑人员，这些规定须知</td>\n",
       "      <td>2020-05-16</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>河南80后会计贪污300万买手办  司法拍卖33万宅男抢破头</td>\n",
       "      <td>2020-05-16</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>河南人的福利！年底前全省县城或将5G全覆盖， 惠及14大类行业</td>\n",
       "      <td>2020-05-15</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>格斗狂人郑州约战一龙却等来警察 一龙回应：自导自演的炒作</td>\n",
       "      <td>2020-05-14</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>446</td>\n",
       "      <td>先分后混！郑州垃圾分类“典范”被曝光后，官方这样回应</td>\n",
       "      <td>2019-11-20</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>447</td>\n",
       "      <td>大象独家 | 一桶垃圾的100个小时</td>\n",
       "      <td>2019-11-19</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>448</td>\n",
       "      <td>智能制造“大比武”！这场国字头大赛为何看好郑州？</td>\n",
       "      <td>2019-11-19</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>449</td>\n",
       "      <td>结婚钻戒属于夫妻共同财产还是个人财产？</td>\n",
       "      <td>2018-07-29</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>450</td>\n",
       "      <td>律师说事：“真假离婚”的法律后果</td>\n",
       "      <td>2018-07-24</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>451 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                               title create_time  \\\n",
       "0     谱写青春战歌 交出合格答卷——六集电视政论片《雄关》反响热烈  2020-05-16   \n",
       "1               @疫情中高风险地区入郑人员，这些规定须知  2020-05-16   \n",
       "2     河南80后会计贪污300万买手办  司法拍卖33万宅男抢破头  2020-05-16   \n",
       "3    河南人的福利！年底前全省县城或将5G全覆盖， 惠及14大类行业  2020-05-15   \n",
       "4       格斗狂人郑州约战一龙却等来警察 一龙回应：自导自演的炒作  2020-05-14   \n",
       "..                               ...         ...   \n",
       "446       先分后混！郑州垃圾分类“典范”被曝光后，官方这样回应  2019-11-20   \n",
       "447               大象独家 | 一桶垃圾的100个小时  2019-11-19   \n",
       "448         智能制造“大比武”！这场国字头大赛为何看好郑州？  2019-11-19   \n",
       "449              结婚钻戒属于夫妻共同财产还是个人财产？  2018-07-29   \n",
       "450                 律师说事：“真假离婚”的法律后果  2018-07-24   \n",
       "\n",
       "                                                  link  \n",
       "0    http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...  \n",
       "1    http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...  \n",
       "2    http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...  \n",
       "3    http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...  \n",
       "4    http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...  \n",
       "..                                                 ...  \n",
       "446  http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...  \n",
       "447  http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...  \n",
       "448  http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...  \n",
       "449  http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...  \n",
       "450  http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...  \n",
       "\n",
       "[451 rows x 3 columns]"
      ]
     },
     "execution_count": 157,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_url_out[~df_url_out.duplicated()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 158,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "      <th>create_time</th>\n",
       "      <th>link</th>\n",
       "      <th>variable</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>谱写青春战歌 交出合格答卷——六集电视政论片《雄关》反响热烈</td>\n",
       "      <td>2020-05-16</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...</td>\n",
       "      <td>青春</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>@疫情中高风险地区入郑人员，这些规定须知</td>\n",
       "      <td>2020-05-16</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...</td>\n",
       "      <td>疫情</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>河南80后会计贪污300万买手办  司法拍卖33万宅男抢破头</td>\n",
       "      <td>2020-05-16</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...</td>\n",
       "      <td>无法分类</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>河南人的福利！年底前全省县城或将5G全覆盖， 惠及14大类行业</td>\n",
       "      <td>2020-05-15</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...</td>\n",
       "      <td>无法分类</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>格斗狂人郑州约战一龙却等来警察 一龙回应：自导自演的炒作</td>\n",
       "      <td>2020-05-14</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...</td>\n",
       "      <td>无法分类</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>446</td>\n",
       "      <td>先分后混！郑州垃圾分类“典范”被曝光后，官方这样回应</td>\n",
       "      <td>2019-11-20</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...</td>\n",
       "      <td>官方</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>447</td>\n",
       "      <td>大象独家 | 一桶垃圾的100个小时</td>\n",
       "      <td>2019-11-19</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...</td>\n",
       "      <td>大象独家</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>448</td>\n",
       "      <td>智能制造“大比武”！这场国字头大赛为何看好郑州？</td>\n",
       "      <td>2019-11-19</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...</td>\n",
       "      <td>智能</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>449</td>\n",
       "      <td>结婚钻戒属于夫妻共同财产还是个人财产？</td>\n",
       "      <td>2018-07-29</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...</td>\n",
       "      <td>无法分类</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>450</td>\n",
       "      <td>律师说事：“真假离婚”的法律后果</td>\n",
       "      <td>2018-07-24</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...</td>\n",
       "      <td>无法分类</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>451 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                               title create_time  \\\n",
       "0     谱写青春战歌 交出合格答卷——六集电视政论片《雄关》反响热烈  2020-05-16   \n",
       "1               @疫情中高风险地区入郑人员，这些规定须知  2020-05-16   \n",
       "2     河南80后会计贪污300万买手办  司法拍卖33万宅男抢破头  2020-05-16   \n",
       "3    河南人的福利！年底前全省县城或将5G全覆盖， 惠及14大类行业  2020-05-15   \n",
       "4       格斗狂人郑州约战一龙却等来警察 一龙回应：自导自演的炒作  2020-05-14   \n",
       "..                               ...         ...   \n",
       "446       先分后混！郑州垃圾分类“典范”被曝光后，官方这样回应  2019-11-20   \n",
       "447               大象独家 | 一桶垃圾的100个小时  2019-11-19   \n",
       "448         智能制造“大比武”！这场国字头大赛为何看好郑州？  2019-11-19   \n",
       "449              结婚钻戒属于夫妻共同财产还是个人财产？  2018-07-29   \n",
       "450                 律师说事：“真假离婚”的法律后果  2018-07-24   \n",
       "\n",
       "                                                  link variable  \n",
       "0    http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...       青春  \n",
       "1    http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...       疫情  \n",
       "2    http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...     无法分类  \n",
       "3    http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...     无法分类  \n",
       "4    http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...     无法分类  \n",
       "..                                                 ...      ...  \n",
       "446  http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...       官方  \n",
       "447  http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...     大象独家  \n",
       "448  http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...       智能  \n",
       "449  http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...     无法分类  \n",
       "450  http://mp.weixin.qq.com/s?__biz=MzU1OTYxOTI3NQ...     无法分类  \n",
       "\n",
       "[451 rows x 4 columns]"
      ]
     },
     "execution_count": 158,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_o = df_url_out.join(df_cat).replace(\"\", np.nan).fillna(\"无法分类\")\n",
    "df_o"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 159,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>variable</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>无法分类</td>\n",
       "      <td>252</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>武汉</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>疫情</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>疫</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>肺炎</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>电影</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>开学</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>医院</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>隔离</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>湖北</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>口罩</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020年</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>小学</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>专家</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>高校</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>复工</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>出行</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>五一</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>儿童</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>官方</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>全球</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>健康码</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>青春</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>直播</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>平台</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>开放</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>城市</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>短视频</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>解密</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>入境</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>大象独家</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>停课</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>智能</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>粤港澳大湾区</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>乘车</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>被骗</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>中学</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>复课</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          title\n",
       "variable       \n",
       "无法分类        252\n",
       "武汉           20\n",
       "疫情           19\n",
       "疫            13\n",
       "肺炎           12\n",
       "电影           11\n",
       "开学           10\n",
       "医院           10\n",
       "隔离            8\n",
       "湖北            7\n",
       "口罩            7\n",
       "2020年         6\n",
       "小学            6\n",
       "专家            6\n",
       "高校            6\n",
       "复工            5\n",
       "出行            5\n",
       "五一            5\n",
       "儿童            4\n",
       "官方            4\n",
       "全球            4\n",
       "健康码           3\n",
       "青春            3\n",
       "直播            3\n",
       "平台            3\n",
       "开放            3\n",
       "城市            3\n",
       "短视频           2\n",
       "解密            2\n",
       "入境            1\n",
       "大象独家          1\n",
       "停课            1\n",
       "智能            1\n",
       "粤港澳大湾区        1\n",
       "乘车            1\n",
       "被骗            1\n",
       "中学            1\n",
       "复课            1"
      ]
     },
     "execution_count": 159,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_stats = df_o.groupby(by=\"variable\").agg({\"title\":\"count\"}).sort_values(by=\"title\", ascending=False)\n",
    "df_stats"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 输出"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 160,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_account.columns.name = \"rel_accounts\"\n",
    "df_o.columns.name = \"url_cat\"\n",
    "df_stats.columns.name = \"stats\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 161,
   "metadata": {},
   "outputs": [],
   "source": [
    "_df_.columns.name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 162,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get the xlsxwriter workbook and worksheet objects.  \n",
    "with pd.ExcelWriter(fn[\"output\"][\"公众号_xlsx\"].format(公众号=公众号)) as writer:\n",
    "    workbook  = writer.book\n",
    "\n",
    "    for _df_ in [df_account, df_o, df_stats]:\n",
    "        _df_.to_excel(writer, sheet_name = _df_.columns.name)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
